# coding=UTF-8
"""
采用非的思想，但估计又行不通
"""
import re
import xlrd
import openpyxl
from os import listdir
from os.path import splitext


def write_new_xlsx(path, value: dict, sheet_name='send_to_song'):
    workbook = openpyxl.Workbook()
    sheet = workbook.active
    sheet.title = sheet_name
    i = 0
    for k, v in value.items():
        i += 1
        sheet.cell(row=i, column=1, value=k)
        text = ''
        for v_v in v:
            text += v_v
        sheet.cell(row=i, column=2, value=text)
    workbook.save(path.replace('.xlsx', '.非选择题.xlsx'))
    print("{}表格写入数据成功！".format(path))


def cutting_options(file_path):
    """
    切割出四个选项以及问题,返回完美字典  {问题:答案}
    :param file_path: 完整的xlsx文件路径
    :return: {问题:[A,B,C,D,right]}
    """
    wb = xlrd.open_workbook(file_path)
    sh = wb.sheet_by_name('send_to_song')
    # print('元数据行数 ', sh.nrows, end='\n')  # 有效数据行数
    list_only_question = []  # 中间件列表,用来过滤掉非选择题
    list_only_answer = []  # 中间件列表,用来过滤掉非选择题
    for i in range(sh.nrows):
        # 判断是否四个选项都在这个题里,在就加入中间件列表,奇数项表示他的答案
        if 'A.' not in sh.cell(i, 0).value \
                and 'B.' not in sh.cell(i, 0).value \
                and 'C.' not in sh.cell(i, 0).value \
                and 'D.' not in sh.cell(i, 0).value:
            list_only_question.append(sh.cell(i, 0).value)
            list_only_answer.append(sh.cell(i, 1).value)
    print(len(list_only_question), len(list_only_answer))
    # print(list_only_question)
    """匹配问题以及选项，永远不要忘记转义！还有元字符的使用"""
    zh_symbol = re.compile(r"[\u3002|\uff1f|\uff01|\uff0c|\u3001|\uff1b|\uff1a|\u201c|\u201d"
                           r"|\u2018|\u2019|\uff08|\uff09|\u300a|\u300b|\u3008|\u3009|\u3010"
                           r"|\u3011|\u300e|\u300f|\u300c|\u300d|\ufe43|\ufe44|\u3014|\u3015"
                           r"|\u2026|\u2014|\uff5e|\ufe4f|\uffe5]", re.S)
    en_symbol = re.compile(r"[\.!@#\$%\\\^&\*\)\(\+=\{\}\[\]\/\",\'<>~\·`\?:;|]", re.S)
    question_obj = re.compile(r'^(?P<Q>.*?)A\.', re.S)
    """
    一个列表选项，一个列表答案，长度相同
    创建一个新字典，用来做完美字典{问题:正确答案}
    """
    perfect_dictionary = {}
    li_count = 0  # 该计数器步进为1单独用于答案列表
    for li in list_only_question:
        ret_zh = zh_symbol.finditer(li)
        ret_en = en_symbol.finditer(li)
        q_mid = li.replace('<br>', '').replace(' ', '')
        for symbol in ret_zh:
            # 去除中文标点符号
            q_mid = q_mid.replace(symbol.group(), '')
        for symbol in ret_en:
            # 去除英文标点符号
            q_mid = q_mid.replace(symbol.group(), '')
        # 问题继续做字典的键，值设置成一个空列表
        perfect_dictionary[q_mid] = list_only_answer[li_count]
        li_count += 1
    # print(perfect_dictionary)
    # print(sh.cell(0, 0).value)  # 输出第一行第一列的值
    # print(sh.row_values(0))  # 输出第一行的所有值
    # 将数据和标题组合成字典
    # 创建新字典,键值不为空,就不写入
    # final_dict = {}
    # for i in range(sh.nrows):
    #     if sh.cell(i, 1).value != '' and sh.cell(i, 1).value != ' ':
    #         final_dict[sh.cell(i, 0).value] = sh.cell(i, 1).value
    # print('精简后行数 ', len(final_dict), end='')  # 有效数据行数
    # print('精简比', sh.nrows / len(final_dict))
    return perfect_dictionary


def get_all_xlsx(path_dir_='D:\\send_to_song\\'):
    """
    接受文件夹路径，返回所有xlsx文件路径的列表
    :param path_dir_:
    :return:
    """
    filenames_lst = listdir(path_dir_)
    xlsx_lst_ = []
    for item in filenames_lst:
        if splitext(item)[1] == '.xlsx':
            xlsx_lst_.append('D:\\send_to_song\\{}'.format(str(item)))
    return xlsx_lst_


if __name__ == '__main__':
    filepath = 'D:\\send_to_song\\虚拟化_高云强_大题有答案没精简.精简副本.xlsx'
    pf_dict = cutting_options(filepath)  # 返回完美字典
    print(pf_dict.keys())
    # all_xlsx_path = get_all_xlsx("D:\\send_to_song\\")
    # for xlsx_path in all_xlsx_path:
    #     perfect_dict = cutting_options(xlsx_path)
    #     write_new_xlsx(xlsx_path, perfect_dict)
